Chapter {seq chapter}
{seq chapter sprfile}	Series 3/3a and MC Spreadsheet File Format{tc "{seq chapter sprfile}	Series 3/3a and MC Spreadsheet File Format"}
This document describes the structure of the .spr files that are used by the MC, Series 3 and Series 3a spreadsheets. The description is to a level that allows other software to read and write non-password-protected spreadsheet files.
File header{tc "File header" \l 2}
The spreadsheet starts with the following header:
TEXT fid[16]="SPREADSHEET"	packed with trailing zeros
UWORD vers=0
UWORD offset=0
UWORD rtvers=0
This header must be supplied exactly as described, otherwise all current versions of the Spreadsheet will refuse to load the file. The only exception is that a password-protected Series 3a spreadsheet file has a file type identifier string (in fid[]) of "SPREADSHEETB". This prevents it from being recognised on Series 3 and MC machines, where password protection of spreadsheet files is not supported.
Records{tc "Records" \l 2}
The remainder of the file consists of type/length records, each of which has the following structure:
UWORD type
UWORD len
record body
the record type
the record length
len bytes of data

The structure of the data in the record body is dependent on the record type. Currently the following types are defined:
1	Formula
2	Cell contents
3	Column width
4	Default column width
5	Status information
6	Display information
7	Named range
8	Print range
9	Database/criteria ranges
10	Table
11	Print setup (MC)
12	Font (MC)
13	Graph (S3)
14	Current graph index (S3)
15	Font palette (S3)
16	Print data (S3)
17	Printer model (S3)
18	Header text (S3)
19	Footer text (S3)
20	Display extras (S3)	
21	3a Display extras (S3a)
22	Password (S3a)
As indicated in the above table, some of the above types are specific to the MC version and some to the Series 3/3a.
In general records can appear in any order, but there are currently three exceptions:
	All formula (type 1) records must appear before the cell (type 2) records that access them, and the order in which the formula records appear must be preserved.
	The 3a Display extras (type 21) record, when present, should immediately precede the Display information (type 6) record. Otherwise the 3a Display extras record will be ignored.
	The Display extras (type 20) record, when present, should immediately precede the 3a Display extras (type 21) record, if one exists. In the absence of a 3a Display extras record, the Display extras record should immediately precede the Display information (type 6) record. Otherwise the Display extras record will be ignored.
Some record types may appear many times, and not all record types need be present. Some record types are expected to appear only once in the file. If more than one of each record of such a type is present, the last record will be read and earlier records of the same type will be ignored. The number of times that a particular type of record is expected to occur is indicated in the following descriptions of the record types.
Range references{tc "Range references" \l 3}
Within any record, a range reference specifies the top left and bottom right cells of the range in column and row units. These references are inclusive so that, for example, the range reference {{0,0},{1,2}} describes the range A1:B3.
Formula record (type 1, variable length){tc "Formula record (type 1, variable length)" \l 3}
A Spreadsheet file may contain any number of Formula records. 
Each formula is stored separately from the cell or cells that use it. This allows memory savings to be made by storing only one copy of a commonly used formula.
The order in which the formula records appear is significant. This determines the index, counting from zero, used to reference a formula from a cell record.
The body of a Formula record is structured as follows:
UWORD use
UBYTE len
UBYTE form[len]
usage count
formula length (253 bytes maximum)
the formula

The formula is stored in reverse Polish notation (RPN). Each operand is preceded by a byte that identifies its type. Each function or operator is identified by a single byte that follows the operands upon which it acts.
Brackets are stored in the formula exactly as they were entered, but are ignored when a formula is evaluated. They are retained only so that the formula can be reproduced and displayed in exactly the same form as it was typed in.
The RPN structure is broken for the set of functions that act upon argument lists of variable length (AVG, COUNT, MAX, MIN, STD, SUM and VAR). For these functions a Start token precedes the argument list and an End token follows it. In addition, each operand within the list is preceded by a special token.
The tokens used in formulae are as follows:
Operators
	0x01	Less than
	0x02	Less than or equal
	0x03	Greater than
	0x04	Greater than or equal
	0x05	Not equal
	0x06	Equal
	0x07	Add
	0x08	Subtract
	0x09	Multiply
	0x0a	Divide
	0x0b	Power
	0x0c	Unary plus
	0x0d	Unary minus
	0x0e	Logical NOT
	0x0f	Logical AND
	0x10	Logical OR
	0x11	String concatenate
Delimiters
	0x12	Open bracket
	0x13	Close bracket
	0x14	Comma
	0x15	End of formula
Operands
	0x16	Double constant (IEEE floating point number)
	0x17	Integer constant (WORD)
	0x18	Text string (leading-byte-counted string)
	0x19	Cell reference (WORD col, WORD row)
	0x1a	Range reference (WORD tlCol, WORD tlRow, WORD brCol, WORD brRow)
Column and/or row references can be either absolute or relative. If the top bit of the WORD is set, the reference is relative, otherwise the reference is absolute. Relative references are treated as a signed offset (ignoring the top bit) from the cell that uses the formula. Note that cell A1 is 0,0.
Functions
In the following list, 'x' refers to a numeric argument, 'str' a string and 'range' a range reference.
	0x1b	Err
	0x1c	False
	0x1d	Na
	0x1e	Pi
	0x1f	Rand
	0x20	Now
	0x21	True
	0x22	Abs(x)
	0x23	Acos(x)
	0x24	Asin(x)
	0x25	At(str)
	0x26	Atan(x)
	0x27	Cellpointer(x)
	0x28	Char(x)
	0x29	Code(str)
	0x2a	Cols(range)
	0x2b	Cos(x)
	0x2c	Datevalue(str)
	0x2d	Day(x)
	0x2e	Exp(x)
	0x2f	Hour(x)
	0x30	Int(x)
	0x31	Iserr(range)
	0x32	Isna(range)
	0x33	Isnum(range)
	0x34	Isstr(range)
	0x35	Len(str)
	0x36	Ln(x)
	0x37	Log(x)
	0x38	Lower(str)
	0x39	Minute(x)
	0x3a	Month(x)
	0x3b	N(range)
	0x3c	Proper(str)
	0x3d	Rows(range)
	0x3e	S(range)
	0x3f	Second(x)
	0x40	Sin(x)
	0x41	Sqrt(x)
	0x42	Tan(x)
	0x43	Timevalue(str)
	0x44	Trim(str)
	0x45	Upper(str)
	0x46	Value(str)
	0x47	Year(x)
	0x48	Atan2(x,x)
	0x49	Cell(x,range)
	0x4a	Exact(str,str)
	0x4b	Irr(x,x)
	0x4c	Left(str,x)
	0x4d	Mod(x,x)
	0x4e	Npv(x,x)
	0x4f	Not used
	0x50	Repeat(str,x)
	0x51	Right(str,x)
	0x52	Round(x,x)
	0x53	String(x,x)
	0x54	Cterm(x,x)
	0x55	Date(x,x)
	0x56	Davg(range,x,range)
	0x57	Dcount(range,x,range)
	0x58	Dmax(range,x,range)
	0x59	Dmin(range,x,range)
	0x5a	Dstd(range,x,range)
	0x5b	Dsum(range,x,range)
	0x5c	Dvar(range,x,range)
	0x5d	Find(str,str,x)
	0x5e	Fv(x,x,x)
	0x5f	Hlookup(x,range,x)
	0x60	If(x,x,x)
	0x61	Index(range,x,x)
	0x62	Mid(str,x,x)
	0x63	Pmt(x,x,x)
	0x64	Pv(x,x,x)
	0x65	Rate(x,x,x)
	0x66	Sin(x)
	0x67	Term(x,x,x)
	0x68	Time(x,x,x)
	0x69	VLookup(range,x,x)
	0x6a	Ddb(x,x,x,x)
	0x6b	Replace(str,x,x,str)
	0x6c	Syd(x,x,x,x)
	0x6d	End of avg()
	0x6e	End of choose()
	0x6f	End of count()
	0x70	End of max()
	0x71	End of min()
	0x72	End of std()
	0x73	End of sum()
	0x74	End of var()
	0x75	Start of avg()
	0x76	Start of choose()
	0x77	Start of count()
	0x78	Start of max()
	0x79	Start of min()
	0x7a	Start of std()
	0x7b	Start of sum()
	0x7c	Start of var()	
	0x7d	Range in avg()
	0x7e	Range in choose()
	0x7f	Range in count()
	0x80	Range in max()
	0x81	Range in min()
	0x82	Range in std()
	0x83	Range in sum()
	0x84	Range in var()	
	0x85	Cell in avg()
	0x86	Cell in choose()
	0x87	Cell in count()
	0x88	Cell in max()
	0x89	Cell in min()
	0x8a	Cell in std()
	0x8b	Cell in sum()
	0x8c	Cell in var()
Cell record (type 2, variable length){tc "Cell record (type 2, variable length)" \l 3}
A record of this type describes a single spreadsheet cell. There may be any number of these records in the file.
The body of a Cell record is structured as follows:
UWORD column
UWORD row
UBYTE flags
UBYTE format
cell contents
Cell co-ordinates (cell A1 is 0,0)

See below
See below


Flags
The flags byte contains information about the display alignment of the cell and the cell type.
X
X
X
XX
XXX
used by the natural order sort and should be left as is
changed flag, TRUE if (and only if) the cell has changed since the last recalc
numeric alignment, 1 left aligned, 0 right aligned
text alignment, 00 repeated, 01 left, 10 right, 11 centered
cell type, defines the format of the contents data as follows:
000 (0) Blank - contains only the format data shown above and has no contents data
001 (1) Double - contains a floating point constant as a DOUBLE value
010 (2) Text - contains leading-byte-counted text
011 (3) Integer - contains an integer (WORD) constant
101 (5) Double formula - contains a formula that evaluates to a numeric result. The contents data is a WORD containing the formula reference, immediately followed by a DOUBLE that contains the current resultant value for the cell
110 (6) Text formula - The cell contains a formula that evaluates to a text result. The contents data is a WORD containing the formula reference, immediately followed by the leading-byte counted text that represents the current resultant value for the cell.

Each formula reference is an index, counting from zero, determined by the order in which the formula records appear in the file.
Format
The format byte records whether or not the cell is currently protected and the way in which numeric values should be displayed.
X
XXXXXXX
Set if the cell is currently protected
Gives the numeric display format as follows:
000XXXX	Fixed (XXXX decimal places)
001XXXX	Scientific (XXXX decimal places)
010XXXX	Currency (XXXX decimal places)
011XXXX	Percentage (XXXX decimal places)
100XXXX	Comma (XXXX decimal places)
1110000	Bargraph
1110001	General
1110010	Date (Lotus DD-MM-YY)
1110101	Show formulae
1110110	Hidden
1110111	Time	(Lotus HH:MM:SS)
1111111	Default

For the MC Spreadsheet this is the full extent of the cell record. In files generated on the Series 3/3a there is an extra trailing byte, following the cell details described above. This extra byte contains a font style number, in the range 0-3 inclusive, allowing selection of one of the four fonts that are defined by a Font palette (type 15) record. The presence or absence of this additional byte can be deduced by calculating the length of the other data in the record and subtracting this value from from the length of the record.
Column width (type 3, length 2){tc "Column width (type 3, length 2)" \l 3}
A record of this type is present for each column that is not of the default width. The record body is as follows:
UBYTE column
UBYTE width
the column number (0 for column A)
the width of the column (in characters)


Default column width (type 4, length 2){tc "Default column width (type 4, length 2)" \l 3}
A Spreadsheet file contains only one record of this type. The record body is as follows:
UWORD width
width (in characters)

This record specifies the width of all columns for which there is no specific Column width (type 3) record.
Status information (type 5, length 4){tc "Status information (type 5, length 4)" \l 3}
A Spreadsheet file contains only one record of this type. It stores assorted information that relates to the spreadsheet as a whole. The body of a Status information record is structured as follows:
UWORD flags
UBYTE defForm
UBYTE defAlign
see below
the default numeric display format
the default text and numeric alignments

The numeric display format is one of the values described earlier for a Cell (type 2) record. It is the display format used for all cells whose Cell record specifies the default numeric display format. Clearly, defForm may not itself be set to the default value.
The value of defAlign contains the default text and numeric alignments, as described earlier for a Cell (type 2) record. Each newly created cell is set to use the alignments specified by defAlign.
Flags
The four least significant bits of flags are used for the following purposes:
X
X
X
X
set if auto recalc is on
set is protection override is on
set if cells have been deleted since last recalc
set if Table recalc is on

Display information (type 6, length 26){tc "Display information (type 6, length 26)" \l 3}
Only one Display information record is expected to appear in the file. The body of a record of this type contains the current state of the display for the spreadsheet. It is structured as follows:
UWORD titleTlCol
UWORD titleTlRow
UWORD titleBrCol
UWORD titleBrRow
UWORD topCol
UWORD topRow
UWORD selTlCol
UWORD selTlRow
UWORD selBrCol
UWORD selBrCol
UWORD cursorCol
UWORD cursorRow
UBYTE lines
UBYTE hideZeros
range for the titles





select range



position of cursor

TRUE if grid lines are to be displayed
TRUE if zero values are hidden

Named range (type 7, length 26){tc "Named range (type 7, length 26)" \l 3}
Each Named range record specifies a range or cell to be associated with a name. There may be any number of such records in a file. The body of the record is structured as follows:
TEXT name[16]
UWORD range_left_column
UWORD range_top_row
UWORD range_right_column
UWORD range_bottom_row
UWORD type
zero terminated text string
the range associated with name



reference type

The value of type is 25 (0x19) for a cell reference and 26 (0x1a) for a range reference. These values are chosen to match the reference tokens used in formulae.
Print range (type 8, length 8){tc "Print range (type 8, length 8)" \l 3}
A record of this type specifies a range to be offered for selective printing. There may be any number of these records in the file. The body of a Print range record is structured as follows:
UWORD range_left_column
UWORD range_top_row
UWORD range_right_column
UWORD range_bottom_row
the range

Database, criterion settings (type 9, length 16){tc "Database, criterion settings (type 9, length 16)" \l 3}
A record of this type specifies the criterion and database ranges to be used by the database commands. A Spreadsheet file is expected to contain zero or one Database/criterion records. The body of the record is structured as follows:
UWORD crit_left_col
UWORD crit_top_row
UWORD crit_right_col
UWORD crit_bottom_row
UWORD dbase_left_col
UWORD dbase_top_row
UWORD dbase_right_col
UWORD dbase_bottom_row
criterion range



database range


Table information (type 10, length 16){tc "Table information (type 10, length 16)" \l 3}
A Spreadsheet file is expexted to contain zero or one Table information records. The body of the record is structured as follows:
UWORD range_left_col
UWORD range_top_row
UWORD range_right_col
UWORD range_bottom_row
UWORD input1_col
UWORD input1_row
UWORD input2_col
UWORD input2_row
table range



input cell 1

input cell 2

For table 2 both input cells are valid. For table 1 input2_col must be set to 0xffff (65535).
Print setup (type 11, length 2){tc "Print setup (type 11, length 2)" \l 3}
The Print setup record only appears in an MC Spreadsheet file, and only only one such record is expected to appear in the file. The body of the record is structured as follows:
UWORD type
Allowed values for type are:
X
X
X
X
if TRUE show values, else show formulae
if TRUE, show hidden cells
if TRUE, show column seperators
if TRUE, show headers

Font (type 12, length 18){tc "Font (type 12, length 18)" \l 3}
The Font record only appears in an MC Spreadsheet file, and only only one such record is expected to appear in the file. The body of the record is structured as follows:
UWORD type
TEXT name[16]

font name

The value of type specifies the associated style, as indicated below:
X
X
bold
double height

Graph (type 13, variable length){tc "Graph (type 13, variable length)" \l 3}
A Graph record only appears in a Series 3/3a Spreadsheet file, and there may be any number of records of this type in the file. Each record defines a displayable graph. The body of the record is structured as follows:
TEXT name[16]
UWORD A_range[4]
UWORD B_range[4]
UWORD C_range[4]
UWORD D_range[4]
UWORD E_range[4]
UWORD F_range[4]
UWORD X_range[4]
UWORD A_labels[4]
UWORD B_labels[4]
UWORD C_labels[4]
UWORD D_labels[4]
UWORD E_labels[4]
UWORD F_labels[4]
UBYTE fmts[6]
UBYTE aligns[6]
UBYTE xAxisScaling
UBYTE xAxisFormat
DOUBLE xAxisLowerLimit
DOUBLE xAxisUpperLimit
UBYTE yAxisScaling
UBYTE yAxisFormat
DOUBLE yAxisLowerLimit
DOUBLE yAxisUpperLimit
UBYTE graphType
UBYTE gridFlags
UBYTE colour
UBYTE rangeFlags
UBYTE labelFlags
UBYTE otherFlags
WORD skip
ten strings
zero terminated name for the graph
a data range left_col, top_row, right_col, bottom_row






range containing labels for data set A


There are ten zero terminated strings packed consecutively, following skip. These strings are, in order:
	First line of title - max length 40
	Second line of title - max len 40
	Title for x axis - max len 40
	Title for y axis - max len 40
	Legend for A range - max len 20
	.
	.
	.
	Legend for F range - max len 20
Current graph index (type 14, length 2){tc "Current graph index (type 14, length 2)" \l 3}
A record of this type only appears in a Series 3/3a Spreadsheet file, and only one Current graph index record is expected to appear in the file. The body of the record is structured as follows:
UWORD curGraph
It contains the index of the graph that is currently selected, as set by the 'Use graph' menu item. Graphs are indexed in the order that the corresponding Graph (type 13) records appear in the file, with an index of 0 referring to the first graph.
Font palette (type 15, length 24){tc "Font palette (type 15, length 24)" \l 3}
A record of this type only appears in a Series 3/3a Spreadsheet file, and only one Font palette record is expected to appear in the file. The body of the record is structured as follows:
SCRLAY_FONT font1
SCRLAY_FONT font2
SCRLAY_FONT font3
SCRLAY_FONT font4
Each SCRLAY_FONT structure determines one of the four selectable fonts and their corresponding styles (bold, italic etc.). The SCRLAY_FONT struct is defined in scrlay.g as:
typedef struct
    {
    UWORD fid;     window server font id
    UWORD style;   font style (eg bold)
    UWORD height;  height of printer font in decipoints
    } SCRLAY_FONT;
Print data (type 16, length 58){tc "Print data (type 16, length 58)" \l 3}
A Print data record only appears in a Series 3/3a Spreadsheet file, and only one record of this type is expected to appear in the file.
This record contains a PRINTER_PARAMS struct that supplies information required to format the document for the printer. It includes, amongst other items, descriptions of the page size and margins, the page numbering style, header and footer position and alignment.
The detailed description of this record is outside the scope of this document. For some further details of this record, and of the other printer-related records (types 17, 18 and 19), see Saving and restoring print context from file in the Printing chapter of the Object Oriented Programming Guide.
Printer model (type 17, variable length){tc "Printer model (type 17, variable length)" \l 3}
A Printer model record only appears in a Series 3/3a Spreadsheet file, and only one record of this type is expected to appear in the file. The body of the record is structured as follows:
UBYTE modelIndex
TEXT ztsDriver
The data consists of a zero terminated string, containing the full path name of the current printer driver file, preceded by a one byte index to the printer model within the file. The default value is:
0
"ROM::BJ.WDR"
Header text (type 18, variable length){tc "Header text (type 18, variable length)" \l 3}
This record type only appears in a Series 3/3a Spreadsheet file, and only one Header text record is expected to appear in the file. The body of the record is structured as follows:
TEXT zts
The zero terminated string contains the text used for the page header when printing.
Footer text (type 19, variable length){tc "Footer text (type 19, variable length)" \l 3}
This record type only appears in a Series 3/3a Spreadsheet file, and only one Footer text record is expected to appear in the file. The body of the record is structured as follows:
TEXT zts
The zero terminated string contains the text used for the page footer when printing.
Display extras (type 20, length 2){tc "Display extras (type 20, length 2)" \l 3}
A Display extras record only appears in a Series 3/3a Spreadsheet file. A record of this type is ignored unless it immediately precedes either the 3a Display extras (type 21) record, if it exists, or the Display information (type 6) record. The body of the record is structured as follows:
UWORD flags
Only two bits of flags are used:
X
X
set if grid labels are shown
set if small font is used (this is over-ridden if a 3a Display extras (type 21) record is present.

3a Display extras (type 21, length 4){tc "3a Display extras (type 21, length 4)" \l 3}
A 3a Display extras record only appears in a Series 3a Spreadsheet file. A record of this type is ignored unless it immediately precedes the Display information (type 6) record. The body of the record is structured as follows:
UBYTE font
UBYTE statwin
WORD  spare
font used
status window
for future expansion

Font
This takes one of the values:
255
8
9
10
11
smallest font available
Swiss 8 pixel font
Swiss 11 pixel font
Swiss 13 pixel font
Swiss 16 pixel font

Statwin
This takes one of the values:
0
1
2
No status window
Small status window
Large status window

Password (type 22, length 18){tc "Password (type 22, length 18)" \l 3}
This record is present only if the spreadsheet is password-protected. Its content is used as part of the spreadsheet encryption/decryption process.

Additional system information

 	{seq chapter sprfile} Series 3/3a and MC Spreadsheet File Format

{page|1}

	{page|1}

	{page|1}


